package org.zjvis.datascience.service.gis;

import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.getTbColumnName;
import static org.zjvis.datascience.common.gis.GisUtils.GisUtil.queryViewCol;

import cn.hutool.core.util.CharUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.google.common.base.Joiner;
import com.google.common.collect.Lists;

import java.io.*;
import java.nio.file.DirectoryNotEmptyException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashSet;
import java.util.List;
import java.util.Objects;
import java.util.Set;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.concurrent.ExecutionException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import javax.servlet.ServletContext;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Lazy;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.zjvis.datascience.common.constant.Constant;
import org.zjvis.datascience.common.constant.DatabaseConstant;
import org.zjvis.datascience.common.dto.DatabaseDTO;
import org.zjvis.datascience.common.dto.DatasetDTO;
import org.zjvis.datascience.common.dto.DatasetImportResDTO;
import org.zjvis.datascience.common.dto.dataset.DatasetJsonInfo;
import org.zjvis.datascience.common.exception.DataScienceException;
import org.zjvis.datascience.common.gis.GisUtils.GisEncodingUtil;
import org.zjvis.datascience.common.gis.GisUtils.GisUtil;
import org.zjvis.datascience.common.model.ApiResultCode;
import org.zjvis.datascience.common.pool.BasePool;
import org.zjvis.datascience.common.util.JwtUtil;
import org.zjvis.datascience.common.util.RestTemplateUtil;
import org.zjvis.datascience.common.vo.gis.GisImportVO;
import org.zjvis.datascience.service.DatabaseService;
import org.zjvis.datascience.service.DatasetCategoryService;
import org.zjvis.datascience.service.SftpConnectService;
import org.zjvis.datascience.service.dataprovider.GPDataProvider;
import org.zjvis.datascience.service.dataset.DatasetService;
import org.zjvis.datascience.service.dataset.ImportDataService;
import org.zjvis.datascience.service.mapper.DatasetMapper;

@Service
public class GisService {
    private final static Logger logger = LoggerFactory.getLogger(GisService.class);

    public static final String SHP_2_PGSQL = "%s -s %s -W %s -k %s %s > %s";

    public static final String ALTER_COLUMN_SQL = "ALTER TABLE %s RENAME gid TO _record_id_;";

    public static final long LANGUAGE_DRIVER_ID_OFFSET = 29L;

    public static final int LANGUAGE_DRIVER_ID_SIZE = 1;

    @Value("${upload.is-win}")
    private Boolean isWindows;

    @Value("${upload.shp2pgsql-path:shp2pgsql}")
    private String shp2pgsqlPath;

    //连接greenplum数据库 用户名
    @Value("${postgres.username}")
    private String gpUsername;
    //连接greenplum数据库 密码
    @Value("${postgres.password}")
    private String gpPassword;

    @Autowired
    private GPDataProvider gpDataProvider;

    @Autowired
    private DatasetService datasetService;

    @Lazy
    @Autowired
    private DatabaseService databaseService;

    @Autowired
    private SftpConnectService sftpConnectService;

    @Autowired
    private BasePool pool;

    @Autowired
    private DatasetCategoryService datasetCategoryService;

    @Autowired
    private RestTemplateUtil restTemplateUtil;

    public List<DatasetDTO> queryGisDataByCategoryId(Long categoryId) {
        return datasetService.queryByCategoryId(categoryId);
    }


    /**
     * 比较是否和view表中有重复的字段名
     *
     * @param viewName
     * @param sourceTbName
     * @param colName
     * @return
     */
    public int checkName(String viewName, String sourceTbName, String colName) {
        String queryViewSql = queryViewCol(viewName);
        List<String> viewCols = queryFromDB(queryViewSql);
        String countSql = getTbColumnName(sourceTbName);
        List<String> sourceCols = queryFromDB(countSql);
        viewCols.removeAll(sourceCols);
        if (viewCols.size() == 0) {
            return 1;
        } else {
            boolean result = viewCols.contains(colName);
            if (result == true) {
                return 0;
            } else {
                return 1;
            }
        }
    }

    public boolean isStartWithNumber(String str) {
        Pattern pattern = Pattern.compile("[0-9]*");
        Matcher isNum = pattern.matcher(str.charAt(0) + "");
        if (!isNum.matches()) {
            return false;
        }
        return true;
    }


    /**
     * 检查是否有和原表中的字段重名
     *
     * @param sourceTbName
     * @param columnName
     * @return
     */
    public String checkColName(String sourceTbName, String columnName) {
        String countSql = getTbColumnName(sourceTbName);
        List<String> columns = queryFromDB(countSql);
        String newName = createColName(columns, columnName);
        return newName;
    }

    public String createColName(List<String> names, String name) {
        String newName;
        if (names.size() == 0) {
            newName = name;
        } else {
            List<String> defaultName = names.stream()
                    .filter(n -> n.matches("^" + name + "(\\([0-9]+\\))?$"))
                    .collect(Collectors.toList());
            List<Integer> order = defaultName.stream()
                    .map(s -> s.equals(name) ? "0" : s.substring(name.length() + 1, s.length() - 1))
                    .map(Integer::parseInt)
                    .sorted()
                    .collect(Collectors.toList());
            if (order.size() == 0) {
                newName = name;
            } else {
                newName = String.format(name + "_%d", order.get(order.size() - 1) + 1);
            }
        }
        return newName;
    }

    /**
     * 上传shapefile
     *
     * @param file
     * @param fileName
     * @param batch
     * @return
     */
    @Deprecated
    public JSONObject uploadShapeFile(MultipartFile file, String fileName, String batch) {
        String filePath = sftpConnectService.getShpFilePathAndCreateDirIfNotExists(batch, fileName);
        JSONObject res = new JSONObject();
        res.put("batch", batch);
        res.put("resInfo", "");
        if (filePath == null || filePath.isEmpty()) {
            res.put("resInfo", "获取文件上传路径失败");
        }
        try {
            FileUtils.copyInputStreamToFile(file.getInputStream(), new File(filePath));
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
            res.put("resInfo", "文件写入失败");
        }

        return res;
    }


    /**
     * 导入到数据库
     *
     * @param vo
     * @return
     */
    public DatasetImportResDTO importData(GisImportVO vo) {
        DatasetImportResDTO res = DatasetImportResDTO.builder().successed(true).build();
        Integer srid = vo.getSrid();
        String batch = vo.getBatch();
        long userId = JwtUtil.getCurrentUserId();
        String filePath = sftpConnectService.getGisFilePath(batch, userId + ".shp");
        String dbfFilePath = sftpConnectService.getGisFilePath(batch, userId + ".dbf");
        String cpgFilePath = sftpConnectService.getGisFilePath(batch, userId + ".cpg");
        String prjFilePath = sftpConnectService.getGisFilePath(batch, userId + ".prj");
        String sqlFilePath = sftpConnectService.getGisFilePath(batch, userId + ".sql");

        String charset = getShapeFileEncoding(dbfFilePath, cpgFilePath);
        //无法自动识别shape file文件编码时，选用用户勾选得编码
        charset = StringUtils.isEmpty(charset) ? vo.getCharset() : charset;
        String proj4Text = getProj4FromPrjFileText(prjFilePath);
        JSONObject gisConfig = new JSONObject();
        gisConfig.put("proj4Text", proj4Text);
        String targetTable = ImportDataService.generateGpTableName();
        String tableName = DatabaseConstant.GREEN_PLUM_DEFAULT_SCHEMA + "." + targetTable;

        String command = "";
        //执行命令生成sql文件
        if (isWindows){
            command = String.format(SHP_2_PGSQL, "\"" +shp2pgsqlPath + "\"", srid, charset, filePath, tableName, sqlFilePath);
        }else {
            command = String.format(SHP_2_PGSQL, "shp2pgsql", srid, charset, filePath, tableName, sqlFilePath);
        }
        logger.info("shape to sql -> {}", command);
        JSONObject res2 = sftpConnectService.executeCommandInLocal(command);
        logger.info("shp2pgsql result -> {}", res2.toJSONString());
        if (res2.getInteger("exitStatus") == 0) {
            //本系统只支持自增id为_record_id_，所以把gid替换成_record_id_
            DatabaseDTO defaultDB = databaseService.queryById(DatabaseConstant.DEFAULT_DATASET_ID);
            try (Connection connection = DriverManager.getConnection(defaultDB.getConnectionURL(), gpUsername, gpPassword)) {
                ScriptRunner runner = new ScriptRunner(connection);
                runner.setDelimiter(";");//语句结束符号设置
                runner.setLogWriter(null);//日志数据输出，这样就不会输出过程
                runner.setSendFullScript(false);
                runner.setAutoCommit(true);
                runner.setStopOnError(true);
                runner.runScript(new InputStreamReader(new FileInputStream(sqlFilePath),"utf-8"));
                Statement statement = connection.createStatement();
                statement.execute(String.format(ALTER_COLUMN_SQL, tableName));
            } catch (SQLException | FileNotFoundException | UnsupportedEncodingException e1) {
                throw new DataScienceException("写入gis文件失败， since " + e1.getMessage(), e1);
            }

            //将相关管表信息存入dataset
            DatasetJsonInfo dj = DatasetJsonInfo.builder()
                    .schema(DatabaseConstant.GREEN_PLUM_DEFAULT_SCHEMA)
                    .table(targetTable)
                    .type(vo.getImportType())
                    .srid(srid)
                    .gisConfig(gisConfig)
                    .build();
            //获取GIS分类id
            if (null == vo.getCategoryId()) {
                vo.setCategoryId(datasetCategoryService
                        .queryCategoryIdByUserIdAndCategoryName(userId, vo.getCategoryName()));
            }else if (vo.getCategoryId() == 0) {
                vo.setCategoryId(datasetCategoryService
                        .queryCategoryIdByUserIdAndCategoryName(userId, vo.getCategoryName()));
            }
            DatasetDTO newDatasetDTO = DatasetDTO.builder()
                    .dataJson(JSON.toJSONString(dj))
                    .categoryId(vo.getCategoryId())
                    .userId(userId)
                    .name(vo.getDatasetName())
                    .gmtCreator(userId)
                    .gmtModifier(userId)
                    .build();
            long newDatasetId = datasetService.insert(newDatasetDTO);
            res.setDatasetIds(Lists.newArrayList(newDatasetId));

            //删除文件
            String batchPath = sftpConnectService.getGisFilePath(batch, null);
            try {
                System.out.println("batchPath -> " +  batchPath);
                Files.deleteIfExists(Paths.get(batchPath));
            }  catch (DirectoryNotEmptyException e2){
                for (File file: Paths.get(batchPath).toFile().listFiles()){
                    file.delete();
                }
            }catch (IOException e1) {
                logger.error("clear file failed, since " + e1.getMessage(), e1);
            }
        } else {
            throw new DataScienceException("文件编码无法识别，请切换至其他文件编码格式重试");
        }
        return res;
    }


    public static String readText(MultipartFile file) {
        String content = null;
        StringBuffer fsb = new StringBuffer();
        try {
            InputStream stream = file.getInputStream();
            InputStreamReader reader = new InputStreamReader(stream,"GB2312");
            BufferedReader buffReader = new BufferedReader(reader);
            while((content = buffReader.readLine())!=null){
                fsb.append(content);
            }
            buffReader.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        content = fsb.toString();

        return content;
    }


    public static List<String> regEx(String patten,String textArea) {
        String pattern = patten;
        Pattern compile = Pattern.compile(pattern);
        Matcher matcher = compile.matcher(textArea);
        List<String> targetList = new ArrayList<String>();
        while (matcher.find()) {
            String substring = textArea.substring(matcher.start(), matcher.end());
            targetList.add(substring);
        }
        return targetList;
    }


    /**
     * 尝试获取用户上传的数据的SRID
     *
     * @param files
     * @return
     */
    public JSONObject getSRID(MultipartFile[] files) {
        JSONObject res = new JSONObject();
        for (MultipartFile file : files) {
            String name = Objects.requireNonNull(file.getOriginalFilename()).toLowerCase();
            if (name.endsWith(".prj")) {
                int srid = GisUtil.STANDARD_SRID;
                String content = readText(file);
                String patten = "(?<=GEOGCS\\[\")[\\s\\S]*?(?=\")";
                List<String> ret = regEx(patten, content);
                if (ret.size()>0) {
                    List<String> parts = new ArrayList<>(Arrays.asList(ret.get(0).split("_")));
                    String sql = "select srid from spatial_ref_sys where srtext ILIKE 'GEOGCS[%" + Joiner.on("_").join(parts.subList(1,parts.size())) + "%'";
                    srid = gpDataProvider.executeQuerySQL("", sql, Integer.class);
                }
                res.put("srid", srid);
                break;
            }
        }
        if (res.containsKey("srid") == false) {
            res.put("srid", "");
        }
        return res;
    }


    /**
     * 多文件上传
     *
     * @param files
     * @param batch
     * @return
     */
    public JSONObject uploadShapeFiles(MultipartFile[] files, String batch) {
        JSONObject res = new JSONObject();
        List<String> success = new CopyOnWriteArrayList();
        List<String> fail = new CopyOnWriteArrayList();
        if (StringUtils.isBlank(batch)) {
            batch = JwtUtil.getCurrentUserId() + "-" + System.currentTimeMillis();
        }
        List<CompletableFuture> futures = new ArrayList<>();
        Set<String> uploadedFile = new HashSet<>();

        for (MultipartFile file : files) {
            String name = Objects.requireNonNull(file.getOriginalFilename()).toLowerCase();
            String suffix = name.substring(name.lastIndexOf(".") + 1).toLowerCase();

            if (file.getSize() == 0) {
                logger.warn("uploadShapeFiles failed, since uploading file's size is 0");
                fail.add(name + "上传失败，原因：" + ApiResultCode.CONTENT_ERROR.getMessage());
            }

            switch (suffix) {
                case "shp":
                case "dbf":
                case "shx":
                case "prj":
                case "cpg":
                    if (uploadedFile.contains(suffix)) {
                        logger.warn("uploadShapeFiles failed, since {}", ApiResultCode.GIS_SHP_FILE_DUPLICATE_SUFFIX_UPLOAD.getMessage());
                        fail.add(name + "上传失败，原因：" + ApiResultCode.GIS_SHP_FILE_DUPLICATE_SUFFIX_UPLOAD.getMessage());
                        break;
                    }
                    uploadedFile.add(suffix);
                    String finalBatch = batch;
                    String filePath = sftpConnectService.getShpFilePathAndCreateDirIfNotExists(finalBatch, name);
                    CompletableFuture future = CompletableFuture.runAsync(() -> {
                        System.out.println("current FilePath -> " + filePath);
                        if (StringUtils.isBlank(filePath)) {
                            fail.add(name + "上传失败，原因：获取文件上传路径失败");
                        } else {
                            try {
                                sftpConnectService.uploadFile(file.getInputStream(), filePath);
                                success.add(name + "上传成功");
                            } catch (Exception e) {
                                fail.add(name + "上传失败，原因：" + e.getMessage());
                                logger.error(e.getMessage(), e);
                            }
                        }
                    }, pool.getExecutor());
                    futures.add(future);
                    break;
                default:
                    logger.warn("uploadShapeFiles failed, since {}", ApiResultCode.GIS_SHP_FILE_SUPPORT.getMessage());
                    fail.add(name + "上传失败，原因：" + ApiResultCode.GIS_SHP_FILE_SUPPORT.getMessage());
                    break;
            }
        }
        //等待所有任务执行完
        try {
            CompletableFuture.allOf(futures.toArray(new CompletableFuture[futures.size()])).get();
        } catch (InterruptedException e) {
            logger.error(e.getMessage(), e);
        } catch (ExecutionException e) {
            logger.error(e.getMessage(), e);
        }
        res.put("success", success);
        res.put("fail", fail);
        res.put("batch", batch);
        return res;
    }

    /**
     * 连接gp数据库并queryData
     * 跟GisAnalysisService。queryFromDB 内容一样
     * @param sql
     * @return queryDbData
     */
    public List<String> queryFromDB(String sql) {
        Connection conn = null;
        List<String> data = new ArrayList<>();
        try {
            List result = gpDataProvider.executeQuerySQL("", sql, List.class);
            data = (List<String>) result.stream().map(Object::toString).collect(Collectors.toList());
//            conn = gpDataProvider.getConn(DEFAULT_DATASET_ID);
//            Statement st = conn.createStatement();
//            ResultSet rs = st.executeQuery(sql);
//            while (rs.next()) {
//                String val = rs.getString(1);
//                data.add(val);
//            }
//            st.close();
//        } catch (SQLException e1) {
//            logger.error("Syntax Error While Executing " + sql + " STATEMENT", e1);
        } catch (Exception e2) {
            logger.error("Gis Executing QuerySQL Error", e2);
        }
//        finally {
//            JDBCUtil.close(conn, null, null);
//        }
        return data;
    }

    /**
     * 判断上传的gis文件中改是否有重复文件
     *
     * @param files
     * @return
     */
    public int checkMultipleFile(List<String> files) {
        Set<String> fileSet = new HashSet<>(files);
        if (files.size() != fileSet.size()) {
            return 1;
        } else {
            return 0;
        }
    }

    /**
     * 判断上传的gis文件中是否为同一份数据
     *
     * @param files
     * @return
     */
    public int checkSameFile(List<String> files) {
        List<String> fileNames = new ArrayList<>();
        files.forEach(e -> {
            fileNames.add(e.substring(0, e.lastIndexOf(".")));
        });
        Set<String> fileNamesSet = new HashSet<>(fileNames);
        if (fileNamesSet.size() != 1) {
            return 1;
        } else {
            return 0;
        }

    }

    public String getProj4FromPrjFileText(String prjFilePath) {
        JSONObject flaskJobInput = new JSONObject();
        flaskJobInput.put("apiPath", "/prj_file_to_proj4");
        try {
            String prjFileText = sftpConnectService.readFirstLineFromFile(prjFilePath);
            if (StringUtils.isBlank(prjFileText)) {
                return null;
            }
            flaskJobInput.put("prj_file_text", prjFileText);
            String proj4Text = restTemplateUtil.submitFlaskJobWebClient(flaskJobInput);
            logger.info(String.format("read from prj file, proj4Text: %s", proj4Text));
            return proj4Text;
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return null;
    }


    /**
     * 获取shape file的编码方式
     * 1.从dbf文件头获取
     * 2.否则从cpg文件中获取
     * 3.默认UTF-8编码
     */
    public String getShapeFileEncoding(String dbfFilePath, String cpgFilePath) {
        String encodingString = null;
        byte[] languageDriverIdArr = sftpConnectService.readSpecificBytesFromFile(dbfFilePath,
                LANGUAGE_DRIVER_ID_OFFSET, LANGUAGE_DRIVER_ID_SIZE);
        byte languageDriverId = languageDriverIdArr[0];
        encodingString = GisEncodingUtil.convertDriverIdToEncodingString(languageDriverId);
        logger.info(String.format("read from dbf file, languageDriverId: %s, encodingString: %s",
                Integer.toHexString(languageDriverId), encodingString));

        if (Objects.isNull(encodingString)) {
            try {
                encodingString = sftpConnectService.readFirstLineFromFile(cpgFilePath);
                logger.info(String.format("read from cpg file, encodingString: %s", encodingString));
            } catch (IOException e) {
                logger.error(e.getMessage(), e);
            }
        }
        return encodingString;
    }
}
